<?php

/**
 *
 * @author Dean
 * @package api.modules.cdom.models
 */

namespace api\modules\openapi\models\report;

use api\modules\internal\fe30\models\ReportBase;
use yii\helpers\ArrayHelper;
use Yii;

class CtripFinancialReport extends ReportBase implements ReportInterface
{
    
    public $per_page;
    public $page;
    public $start_date;
    public $end_date;
    
    const CTRIP_ADVERTISER_ID = 9;
    
    public function rules()
    {
        return [
            [['advertiser_id'],'filter','filter'=>function($value){
                return explode(',', Yii::$app->user->getIdentity()->advertiser_ids)[0];
            }],
            ['page','integer','message'=>'参数page格式不正确'],
            ['per_page','integer','message'=>'参数per_page格式不正确'],
            ['start_date','date','format'=>'yyyy-mm-dd','message'=>'参数start_date格式不正确'],
            ['end_date','date','format'=>'yyyy-mm-dd','message'=>'参数end_date格式不正确'],
            ['start_date','compare', 'compareValue' => date('Y-m-d', strtotime("-1 day")), 'operator' => '<=', 'message'=>'最近只能查昨天的数据'],
            ['end_date','compare', 'compareValue' => date('Y-m-d', strtotime("-1 day")), 'operator' => '<=', 'message'=>'最近只能查昨天的数据'],
            //['start_date','compare', 'compareValue' => date('Y-m-d', strtotime("-90 day")), 'operator' => '>=', 'message'=>'最远只能查90天的数据'],
            //['end_date','compare', 'compareValue' => date('Y-m-d', strtotime("-90 day")), 'operator' => '>=', 'message'=>'最远只能查90天的数据'],
            ['start_date','default','value'=>date('Y-m-d', strtotime("-1 day"))],
            ['end_date','default','value'=>date('Y-m-d', strtotime("-1 day"))],
            ['page','default','value'=>1],
            ['per_page','default','value'=>30],
            ['per_page','number','max'=>500,'message'=>'每页最多返回500个']
        ];
    }
    
    public function getReport()
    {
        $query = (new \yii\db\Query());
        $query->select('report_date,report_hour,campaign_id,adgroup_id,ad_id,advertiser_id,'
                . 'sum(imp) as imp,sum(click) as click,sum(cost) as cost')
              ->from('report_base rb')
              ->where('report_date>0 and report_hour>=0 and ad_id>0 and adgroup_id>0 and campaign_id>0')
              ->andWhere('advertiser_id=:advertiser_id', [':advertiser_id' => self::CTRIP_ADVERTISER_ID]);
        if(!empty($this->start_date)){
            $query->andWhere(['>=', 'report_date', $this->start_date]);
        }
        if(!empty($this->end_date)){
            $query->andWhere(['<=', 'report_date', $this->end_date]);
        }
        $query->groupBy('report_hour,report_date,ad_id,adgroup_id,campaign_id');
        
        $total_num = $query->count('*', Yii::$app->fe3);
        $query->orderBy('report_hour,report_date');
        $query->limit($this->per_page)->offset(($this->page-1)*$this->per_page);
        $report_data = $query->all(Yii::$app->fe3);
//        $commandQuery = clone $query;
//        echo $commandQuery->createCommand()->getRawSql();exit;
//        var_dump(count($report_data));exit;
        $arr_ids = [];
        foreach($report_data as $r){
            if(!in_array($r['ad_id'], $arr_ids)){
                $arr_ids[] = $r['ad_id'];
            }
        }
        
        $ad_data = $this->getAdData($arr_ids);
        
        $data = $this->formatData($report_data, $ad_data);
        
        return [
            'data' => $data,
            'pager' => ['total_num' => $total_num],
            'ext' => ['balance' => $this->getBalance()]
        ];
    }
    
    private function formatData($report_data, $ad_data){
        $result = [];
        foreach($report_data as $r){
            foreach($ad_data as $a){
                if($r['ad_id'] == $a['ad_id']){
//                    $temp = ArrayHelper::merge($r, $a);
                    $temp['report_date'] = $r['report_date'];
                    $temp['report_hour'] = (int)$r['report_hour'];
                    $temp['advertiser_id'] = (int)$r['advertiser_id'];
                    $temp['advertiser_name'] = $a['advertiser_name'];
                    $temp['campaign_id'] = (int)$a['campaign_id'];
                    $temp['campaign_name'] = $a['campaign_name'];
                    $temp['adgroup_id'] = (int)$a['adgroup_id'];
                    $temp['adgroup_name'] = $a['adgroup_name'];
                    $temp['ad_id'] = (int)$a['ad_id'];
                    $temp['ad_name'] = $a['ad_name'];
                    
                    
                    $arr_click_url = self::convertUrl($a['ad_click_url']);
                    $arr_deeplink_url = self::convertUrl($a['deeplink_url']);
                    $temp['allianceid'] = isset($arr_deeplink_url['allianceid']) ? $arr_deeplink_url['allianceid'] : (isset($arr_click_url['allianceid']) ? $arr_click_url['allianceid'] : '');
                    $temp['sid'] = isset($arr_deeplink_url['sid']) ? $arr_deeplink_url['sid'] : (isset($arr_click_url['sid']) ? $arr_click_url['sid'] : '');
                    $temp['ouid'] = isset($arr_deeplink_url['ouid']) ? $arr_deeplink_url['ouid'] : (isset($arr_click_url['ouid']) ? $arr_click_url['ouid'] : '');
                    
                    $temp['imp'] = (int)$r['imp'];
                    $temp['click'] = (int)$r['click'];
                    $temp['ctr'] = $r['imp']> 0 ? number_format($r['click']/$r['imp'], 4,".","")+0 : 0;
                    $temp['cpc'] = $r['click'] > 0 ? number_format($r['cost']/($r['click']*1000000), 2,".","")+0 : 0;
                    $temp['cpm'] = $r['imp'] > 0 ? number_format($r['cost']/($r['imp']*1000), 2,".","")+0 : 0;
                    $temp['cost'] = number_format($r['cost']/1000000,2,".","")+0;
                    $result[] = $temp;
                }
            }
        }
        return $result;
    }
    
    public function getAdData($arr_ids){
        $query = (new \yii\db\Query());
        $query->select('ab.id as ad_id,ab.name as ad_name,agb.id as adgroup_id,'
                . 'agb.name as adgroup_name,cb.id as campaign_id,cb.name as campaign_name,'
                . 'aa.ad_click_url,aa.deeplink_url,sa.name as advertiser_name')
              ->from('ad_base ab')
              ->innerJoin('ad_audit aa', 'ab.id=aa.ad_id')
              ->innerJoin('adgroup_base agb', 'ab.adgroup_id=agb.id')
              ->innerJoin('campaign_base cb', 'ab.campaign_id=cb.id')
              ->innerJoin('system_advertiser sa', 'ab.advertiser_id=sa.id')
              ->where(['in', 'ab.id', $arr_ids])
              ->andWhere('ab.advertiser_id=:advertiser_id', [':advertiser_id' => self::CTRIP_ADVERTISER_ID])
              ->groupBy('ab.id');
//        $commandQuery = clone $query;
//        echo $commandQuery->createCommand()->getRawSql();exit;
        return $data = $query->all(Yii::$app->fe3);
    }
    
    private static function convertUrl($url){
        $result = array();
        $arr = parse_url($url);
        if(isset($arr['query'])){
            $arr_url = explode('&', $arr['query']);
            foreach($arr_url as $v){
                $p = explode('=', $v);
                if(isset($p[1])){
                    $result[$p[0]] = $p[1];
                }
            }
        }
        
        return $result;
    }
    
    private function getBalance(){
        $query = (new \yii\db\Query());
        $result = $query->select('balance')->from('billing_account_balance')
        ->andWhere('advertiser_id=:advertiser_id', [':advertiser_id' => self::CTRIP_ADVERTISER_ID])
        ->scalar(Yii::$app->fe3);
        return number_format($result/1000000, 2, '.', '');
    }
}

?>